﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace HRMService
{
    // NOTE: If you change the class name "Employee" here, you must also update the reference to "Employee" in App.config.
    public partial class MainService : ILabourContract
    {
        public DataSet LabourContract_GetAll()
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        //DataSet LabourContract_GetWithEmployee(int EmployeeID);
        public DataSet LabourContract_GetWithEmployee(int EmployeeID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_GetWithEmployee", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet LabourContract_ConfirmIns(int EmployeeID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_ConfirmIns", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet LabourContract_GetList(out int ItemCount, int currentPage, int pageSize)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_GetList", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public LabourContractInfo LabourContract_GetInfo(int LabourContractID)
        {
            LabourContractInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@LC_ID", LabourContractID);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new LabourContractInfo();
                    retVal.ID = Convert.ToInt32(dr["LC_ID"]);
                    retVal.EmployeeID = Convert.ToInt32(dr["LC_EmployeeID"]);
                    retVal.ContractTypeID = Convert.ToInt32(dr["LC_ContractTypeID"]);
                    retVal.BeginDate = dr["LC_BeginDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["LC_BeginDate"]);
                    retVal.FinishDate = dr["LC_FinishDate"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["LC_FinishDate"]);
                    retVal.BeginProbationPeriod = dr["LC_BeginProbationPeriod"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["LC_BeginProbationPeriod"]);
                    retVal.EndProbationPeriod = dr["LC_EndProbationPeriod"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["LC_EndProbationPeriod"]);
                    retVal.PlaceOfWork = Globals.Object_GetValue<string>(dr["LC_PlaceOfWork"]);
                    retVal.PositionID = Globals.Object_GetValue<int>(dr["LC_PositionID"]);
                    retVal.DepartmentID = Globals.Object_GetValue<int>(dr["LC_DepartmentID"]);
                    retVal.Date = dr["LC_Date"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(dr["LC_Date"]);
                    retVal.Description = Globals.Object_GetValue<string>(dr["LC_Description"]);
                    retVal.IsActive = dr["LC_IsActive"] != DBNull.Value ? Convert.ToBoolean(dr["LC_IsActive"]) : false;
                    retVal.No =  Globals.Object_GetValue<string>(dr["LC_No"]);
                    retVal.Salary = dr["LC_Salary"] !=DBNull.Value ? Convert.ToDecimal(dr["LC_Salary"]) : 0;
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }

        public LabourContractInfo LabourContract_GetWithEmployee_LCActive(int EmployeeID)
        {
            LabourContractInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_GetWithEmployee_LCActive", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new LabourContractInfo();
                    retVal.ID = Convert.ToInt32(dr["LC_ID"]);
                    retVal.EmployeeID = Convert.ToInt32(dr["LC_EmployeeID"]);
                    retVal.ContractTypeID = Convert.ToInt32(dr["LC_ContractTypeID"]);
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet LabourContract_GetMaxID(int EmployeeID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_GetMaxID", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@LC_EmployeeID", EmployeeID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public bool LabourContract_ActiveEmployee(int LCID)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_ActiveEmployee", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@LCID", LCID);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public int LabourContract_Insert(LabourContractInfo eInfo)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@LC_Description",Globals.DB_GetNull( eInfo.Description));
            dbCmd.Parameters.AddWithValue("@LC_EmployeeID", eInfo.EmployeeID);
            dbCmd.Parameters.AddWithValue("@LC_ContractTypeID", eInfo.ContractTypeID);

            if (eInfo.Date == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_Date", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_Date", eInfo.Date);
            
            if(eInfo.BeginDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_BeginDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_BeginDate", eInfo.BeginDate);
            
            if (eInfo.FinishDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_FinishDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_FinishDate", eInfo.FinishDate);
            
            if (eInfo.BeginProbationPeriod == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_BeginProbationPeriod", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_BeginProbationPeriod", eInfo.BeginProbationPeriod);
            
            if (eInfo.EndProbationPeriod == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_EndProbationPeriod", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_EndProbationPeriod", eInfo.EndProbationPeriod);
            
            dbCmd.Parameters.AddWithValue("@LC_PlaceOfWork", Globals.DB_GetNull(eInfo.PlaceOfWork) );
            dbCmd.Parameters.AddWithValue("@LC_PositionID", eInfo.PositionID);
            dbCmd.Parameters.AddWithValue("@LC_DepartmentID", eInfo.DepartmentID);
            dbCmd.Parameters.AddWithValue("@LC_No", Globals.DB_GetNull(eInfo.No));
            dbCmd.Parameters.AddWithValue("@LC_IsActive", eInfo.IsActive);
            dbCmd.Parameters.AddWithValue("@LC_Salary", eInfo.Salary);
            

            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool LabourContract_Update(LabourContractInfo eInfo)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@LC_ID", eInfo.ID);
            dbCmd.Parameters.AddWithValue("@LC_Description", eInfo.Description);
            dbCmd.Parameters.AddWithValue("@LC_EmployeeID", eInfo.EmployeeID);
            dbCmd.Parameters.AddWithValue("@LC_ContractTypeID", eInfo.ContractTypeID);

            if (eInfo.Date == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_Date", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_Date", eInfo.Date);

            if (eInfo.BeginDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_BeginDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_BeginDate", eInfo.BeginDate);

            if (eInfo.FinishDate == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_FinishDate", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_FinishDate", eInfo.FinishDate);

            if (eInfo.BeginProbationPeriod == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_BeginProbationPeriod", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_BeginProbationPeriod", eInfo.BeginProbationPeriod);

            if (eInfo.EndProbationPeriod == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@LC_EndProbationPeriod", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@LC_EndProbationPeriod", eInfo.EndProbationPeriod);
            
            
            dbCmd.Parameters.AddWithValue("@LC_PlaceOfWork", eInfo.PlaceOfWork);
            dbCmd.Parameters.AddWithValue("@LC_PositionID", eInfo.PositionID);
            dbCmd.Parameters.AddWithValue("@LC_DepartmentID", eInfo.DepartmentID);
            dbCmd.Parameters.AddWithValue("@LC_No", eInfo.No);
            dbCmd.Parameters.AddWithValue("@LC_IsActive", eInfo.IsActive);
            dbCmd.Parameters.AddWithValue("@LC_Salary", eInfo.Salary);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public bool LabourContract_UpdateWithEmloyee(int EmployeeID)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_UpdateEmployee", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@EmployeeID", EmployeeID);


            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool LabourContract_Delete(int LabourContractID)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@LC_ID", LabourContractID);

            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet LabourContract_GetWithContractType(int ContractTypeID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_GetWithContractType", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@LC_ContractTypeID", ContractTypeID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet LabourContract_GetReport(int LCID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_LabourContract_GetReport", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@LC_ID", LCID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
    }
}


